﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_LABEL_Select_Paging')
	BEGIN
		DROP Procedure usp_UPDMS_COM_LABEL_Select_Paging
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_LABEL_Select_Paging
**	Desc : 라벨관리을 조회한다.
**	Test Exec Query : Exec usp_UPDMS_COM_LABEL_Select_Paging 0, 10, 'ㄱ','','',''
**	Called by : Com_Dac_UPDMS_COM_LABEL.cs
**	Program ID : Com1017m
**	Auth : 송시명
**	Date : 2011-01-12
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_LABEL_Select_Paging]
@li_st_page int,
@li_ed_page int,
@ls_initial nvarchar(1),
@ls_label_ko nvarchar(200),
@ls_label_en nvarchar(400),
@ls_label_ch nvarchar(400)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT a.Seq,
       a.Seq AS Label_Id,
       a.Label_Ko,
       a.Label_En,
       a.Label_Ch,
       a.Cnt,
       CASE WHEN a.Cnt > 0
	        THEN '<span class="hand" title="조회" onclick="javascript:viewLabelUsingPgmList(''' + CONVERT(NVARCHAR, a.Seq) + ''');">' + CONVERT(VARCHAR, a.Cnt) + '</span>' 
            ELSE ''
       END View_Pgm_List
  FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY Label_Ko ASC) AS Rnum,
               Seq,
               Label_Ko,
               Label_En,
               Label_Ch,
               dbo.ufn_UPDMS_Using_Label_Cnt(Seq) AS Cnt
          FROM (
               SELECT Seq,
                      Label_Ko,
                      Label_En,
                      Label_Ch,
                      CASE WHEN [Label_Ko] < 'ㄱ' THEN SUBSTRING([Label_Ko], 1, 1)
                           WHEN [Label_Ko] < '나' THEN 'ㄱ'
                           WHEN [Label_Ko] < '다' THEN 'ㄴ'
                           WHEN [Label_Ko] < '라' THEN 'ㄷ'
                           WHEN [Label_Ko] < '마' THEN 'ㄹ'
                           WHEN [Label_Ko] < '바' THEN 'ㅁ'
                           WHEN [Label_Ko] < '사' THEN 'ㅂ'
                           WHEN [Label_Ko] < '아' THEN 'ㅅ'
                           WHEN [Label_Ko] < '자' THEN 'ㅇ'
                           WHEN [Label_Ko] < '차' THEN 'ㅈ'
                           WHEN [Label_Ko] < '카' THEN 'ㅊ'
                           WHEN [Label_Ko] < '타' THEN 'ㅋ'
                           WHEN [Label_Ko] < '파' THEN 'ㅌ'
                           WHEN [Label_Ko] < '하' THEN 'ㅍ'
                           WHEN [Label_Ko] < 'a'  THEN 'ㅎ'
                           ELSE SUBSTRING([Label_Ko], 1, 1)
                       END Initial
                  FROM UPDMS_COM_LABEL WITH(NOLOCK)
		     ) z
         WHERE z.Initial LIKE @ls_initial + '%'
           AND z.Label_Ko LIKE '%' + @ls_label_ko + '%'
           AND ISNULL(z.Label_En,'') LIKE '%' + @ls_label_en + '%'
           AND ISNULL(z.Label_Ch,'') LIKE '%' + @ls_label_ch + '%'
       ) a
 WHERE a.Rnum > @li_st_page AND a.Rnum <= @li_ed_page

GO